/**
 * Class:   ActivityRelationsManager
 * 
 * Methods:
 * 
 *      boolean insertActivityBeneficiaryRelation( int activityID,
 *      										   int beneficiaryID );
 *      
 *      boolean insertCurriculumParticipant( int activityID,
 *      									 CurriculumParticipant participant );
 *      
 *      boolean deleteActivityBeneficiaryRelation( int activityID,
 *      										   int beneficiaryID );
 *      
 *      boolean deleteCurriculumParticipant( int activityID, int participantID );
 * 
 * Remarks:
 * 
 * 		This class should not be touched by the controllers.
 * 
 * @author ( Sharmaine Lim )
 * @version ( 2013-02-28 // YYYY-MM-DD )
 */

package managers;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import models.CurriculumParticipant;
import models.Participant;

class CurriculumActivityRelationsManager extends DatabaseManager {
	
    /**
     * Constructors
     */
    
    CurriculumActivityRelationsManager() {}
    
    
    
    /**
     *  Insert Activity_Beneficiary Relation
     *  	- inserts the link of an activity and a beneficiary
     *  	  into the c_activityBeneficiary table
     *  
     *  Parameters
     *		- int activityID: ID of activity to be added to the table
     *		- int beneficiaryID: ID of beneficiary to be added to the
     *				table
     *	
     *	Returns
     *		- boolean true: activity relation is successfully inserted
     * 		- boolean false: insertion failed
     */
    boolean insertActivityBeneficiaryRelation( int activityID,
    												  int beneficiaryID ) {
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement;
        	
    		connect();
        	
    		preparedStatement = getConnection()
    				.prepareStatement( "INSERT INTO `c_activityBeneficiary`" +
    								   " (`activityID`, `beneficiaryID`)" +
    								   " VALUES (?, ?)" );
    		
            preparedStatement.setInt( 1, activityID );
            preparedStatement.setInt( 2, beneficiaryID );
            preparedStatement.executeUpdate();
            
            disconnect();
            return true;
    	}
    	catch ( Exception ex ) {
    		ex.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
    
    
    
    /**
     *  Insert Curriculum Participant
     *  	- inserts the link of an activity and a participant
     *  	  into the c_activityParticipant table
     *  
     *  Parameters
     *		- int activityID: ID of activity to be added to the table
     *		- CurriculumParticipant participant: participant to be
     *				added to the table
     *	
     *	Returns
     *		- boolean true: activity relation is successfully inserted
     * 		- boolean false: insertion failed
     */
    boolean insertCurriculumParticipant( int activityID,
    											CurriculumParticipant participant ) {
		
    	/* Declaration of Local Variables */
		int participantID = new ParticipantManager()
							.getParticipantID( (Participant)participant );
		
		if ( participantID == 0 ) {
    		
    		if ( !( new ParticipantManager()
    				.insertParticipant( (Participant)participant ) ) ) {
    			return false;
    		}
    		
    		participantID = new ParticipantManager()
    						.getParticipantID( (Participant)participant );
    		
    	}
    	
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement;
        	
        	connect();
    		
    		preparedStatement = getConnection()
    							.prepareStatement( "INSERT INTO" +
    											   " `c_activityParticipant`" +
    											   " (`activityID`," +
    											   " `participantID`," +
    											   " `section`, `grade`)" +
    											   " VALUES (?, ?, ?, ?)" );
    		
            preparedStatement.setInt( 1, activityID );
            preparedStatement.setInt( 2, participantID );
            preparedStatement.setString( 3, participant.getSection() );
            preparedStatement.setDouble( 4, participant.getGrade() );
            preparedStatement.executeUpdate();
            
            disconnect();
            return true;
    	}
    	catch ( Exception ex ) {
    		ex.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
	
	
	
	/**
	 *  Search Participants
	 *  	- gets all participants in a certain activity
	 *  	  from the participant database
	 *  
	 *  Parameters
	 *		- int activityID: the ID of the activity the participants
	 *						  are from 
	 *	
	 *	Returns
	 *		- ArrayList<String>.size() == 0: no participants found
	 * 		- ArrayList<String>.size() > 0: participants found
	 */
	ArrayList<CurriculumParticipant> searchParticipants( int activityID ) {
		
		/* Declaration of Local Variables */
		ArrayList<CurriculumParticipant> participantList = new ArrayList<CurriculumParticipant>();
		
		try {
    		/* Declaration of Block Variables */
			Statement statement;
			ResultSet resultSet;
			
			connect();
			
			statement = getConnection().createStatement();
			
			resultSet = statement.executeQuery( "SELECT * FROM" +
												" `c_activityParticipant`" +
												" WHERE `activityID` = " +
												activityID );
			
			disconnect();
			
			while ( resultSet.next() ) {
	    		/* Declaration of Block Variables */
				ResultSet rsParticipant; /* rs stands for resultSet */
				
				connect();
				
				statement = getConnection().createStatement();
				
				rsParticipant = statement
								.executeQuery( "SELECT * FROM `participant`" +
									   		   " WHERE `participantID` = " +
									   		   resultSet.getInt( "participantID" ) );
				
				if ( rsParticipant.next() ) {
					participantList.add( new CurriculumParticipant
										 ( rsParticipant.getString( "participantName" ),
										   rsParticipant.getString( "courseDegree" ),
										   resultSet.getString( "section" ),
										   resultSet.getDouble( "grade" ) ) );
				}
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
		}
		return participantList;
	}
    
    
    
    /**
     *  Delete Activity_Beneficiary Relation
     *  	- deletes the link of an activity and a beneficiary
     *  	  from the c_activityBeneficiary table
     *  
     *  Parameters
     *		- int activityID: ID of activity to be deleted from the
     *				table
     *		- int beneficiaryID: ID of beneficiary to be deleted
     *				from the table
     *	
     *	Returns
     *		- boolean true: activity relation is successfully deleted
     * 		- boolean false: deletion failed
     */
    boolean deleteActivityBeneficiaryRelation( int activityID,
    										   int beneficiaryID ) {
    	try {
    		/* Declaration of Block Variables */
    		String query = "DELETE FROM `c_activityBeneficiary` WHERE" +
					   " `activityID` = ?";
		 	PreparedStatement preparedStatement;
		 	
		 	if ( beneficiaryID != 0 ) {
				query = query.concat( " AND `beneficiaryID` = ?" );
			}
			
			connect();
			
			preparedStatement = getConnection().prepareStatement( query );
			
			preparedStatement.setInt( 1, activityID );
			
			if ( beneficiaryID != 0 ) {
				preparedStatement.setInt( 2, beneficiaryID );
			}
			
			preparedStatement.executeUpdate();
				
		    disconnect();
		    
		    new BeneficiaryManager().deleteBeneficiaryWithNoActivity();
		    
		    return true;
    	}
    	catch ( Exception ex ) {
    		ex.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
    
    
    
    /**
     *  Delete Curriculum Participant
     *  	- deletes the link of an activity and a participant
     *  	  from the c_activityParticipant table
     *  
     *  Parameters
     *		- int activityID: ID of activity to be deleted from the
     *				table
     *		- int participantID: ID of activity to be deleted from the
     *				table
     *	
     *	Returns
     *		- boolean true: activity relation is successfully deleted
     * 		- boolean false: deletion failed
     */
    boolean deleteCurriculumParticipant( int activityID,
    									 int participantID ) {
    	try {
    		/* Declaration of Block Variables */
    		String query = "DELETE FROM `c_activityParticipant` WHERE" +
    					   " `activityID` = ?";
    		PreparedStatement preparedStatement;
    		
    		if ( participantID != 0 ) {
				query = query.concat( " AND `participantID` = ?" );
			}
    		
			connect();
    		
    		preparedStatement = getConnection().prepareStatement( query );
    		
            preparedStatement.setInt( 1, activityID );
            
            if ( participantID != 0 ) {
            	preparedStatement.setInt( 2, participantID );
            }
            
            preparedStatement.executeUpdate();
            
            disconnect();
            
            new ParticipantManager().deleteParticipantWithNoActivity();
            
            return true;
    	}
    	catch ( Exception ex ) {
    		ex.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
}
